package com.wwh.JDBC;

import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Albert
 * @date 2024/8/28-21:38
 * @describe
 */
public class JDBCTemplateTest {
    @Test
    public void dataSourceTest() throws SQLException {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        DataSource dataSource = applicationContext.getBean(DataSource.class);
        System.out.println("dataSource = " + dataSource);
        Connection connection = dataSource.getConnection();
        System.out.println("connection = " + connection);
        connection.close();
    }

    //添加
    @Test
    public void addDataByJdbcTemplate(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

        //添加方式1
        String sql = "INSERT INTO monster VALUES (400, '沙和尚', '降妖宝杖')";
        jdbcTemplate.execute(sql);

        //添加方式2
        sql = "INSERT INTO monster VALUES (?, ?, ?)";
        jdbcTemplate.update(sql, 500, "牛魔王", "芭蕉扇");
    }

    //修改
    @Test
    public void updateDataByJdbcTemplate(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

        //添加方式1
        String sql = "UPDATE monster SET skill=? WHERE id=?";
        jdbcTemplate.update(sql, "三味真火", 500);
    }

    //批量修改
    @Test
    public void batchUpdateDataByJdbcTemplate(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

        String sql = "INSERT INTO monster VALUES (?, ?, ?)";
        List<Object[]> argsList = new ArrayList<>();
        argsList.add(new Object[]{600, "杨戬", "天眼"});
        argsList.add(new Object[]{700, "菩提祖师", "长生不老"});
        jdbcTemplate.batchUpdate(sql, argsList);
    }

    //查询单行结果
    @Test
    public void queryDataByJdbcTemplate(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

        String sql = "SELECT id AS monsterId, name, skill FROM monster WHERE id = ?";
        RowMapper<Monster> rowMapper = new BeanPropertyRowMapper<>(Monster.class);
        Monster monster = jdbcTemplate.queryForObject(sql, rowMapper, 500);

        System.out.println("monster = " + monster);
    }

    //查询多个结果
    @Test
    public void queryMulDataByJdbcTemplate(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

        String sql = "SELECT id AS monsterId, name, skill FROM monster WHERE id >= ?";
        RowMapper<Monster> rowMapper = new BeanPropertyRowMapper<>(Monster.class);
        List<Monster> monsters = jdbcTemplate.query(sql, rowMapper, 200);
        for (Monster monster : monsters) {
            System.out.println("monster = " + monster);
        }

    }

    //查询单行单列的结果
    @Test
    public void queryScalarDataByJdbcTemplate(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

        String sql = "SELECT name FROM monster WHERE id = ?";
        String name = jdbcTemplate.queryForObject(sql, String.class, 200);

        System.out.println("name = " + name);


    }

    //使用具名参数查询
    @Test
    public void updateDataByNamedParameterJdbcTemplate(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = ioc.getBean(NamedParameterJdbcTemplate.class);

        String sql = "INSERT INTO monster VALUES (:id, :name, :skill)";
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("id", 800);
        paramMap.put("name", "哮天犬");
        paramMap.put("skill", "汪汪叫");
        int update = namedParameterJdbcTemplate.update(sql, paramMap);

        System.out.println("affected = " + update);
    }

    //使用sglparametersoruce封装具名参数添加数据
    @Test
    public void updateDataByNamedParameterJdbcTemplateSglParameterSource(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = ioc.getBean(NamedParameterJdbcTemplate.class);

        String sql = "INSERT INTO monster VALUES (:monsterId, :name, :skill)";
        Monster monster = new Monster(900, "白龙马", "载人载货");
        BeanPropertySqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(monster);
        int update = namedParameterJdbcTemplate.update(sql, sqlParameterSource);

        System.out.println("affected = " + update);
    }

    //测试MonsterDAO
    @Test
    public void testMonsterDAO(){
        ApplicationContext ioc = new ClassPathXmlApplicationContext("JDBCTemplate_ioc.xml");
        MonsterDAO monsterDAO = ioc.getBean(MonsterDAO.class);

        Monster monster = new Monster(1000, "六耳猕猴", "假扮孙悟空");
        monsterDAO.save(monster);
    }

}
